package com.mbbmap.security.manager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;
import com.mbbmap.app.manager.SystemLogManager;
import com.mbbmap.util.Constants;
import com.mbbmap.util.EnhancedException;
import com.mbbmap.util.MD5Helper;

public class UserManager {
	static int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	
	public static boolean validatePassword(String oldPassword, String loggedUserID) throws SQLException{
		
		boolean bResult = false;
		
		SystemLogManager sysLogMgr = SystemLogManager.getInstance();
		DBConnectionManager connMgr = DBConnectionManager.getInstance();
		String connectionPool = ConfigManager.getInstance().getConnectionPool();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		
		Connection con = connMgr.getConnection(connectionPool, TIMEOUT);
		PreparedStatement ps = null;
		ResultSet rs= null;

		try {
			if (con == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation("UserManager.CheckPassword [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			}else{
				String strSQL="";
				strSQL="SELECT COUNT(*) FROM tbluser WHERE login_id=? and password=?";
				ps = con.prepareStatement(strSQL);
				ps.setString(1, loggedUserID);
				ps.setString(2, MD5Helper.hash(oldPassword));
				rs = ps.executeQuery();

				if (rs.next()){
					bResult = rs.getInt(1) > 0;
				}
			
			} 

		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("UserManager.userExists [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("UserManager.userExists [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (ps != null) {
					ps.close();
			}
			if (rs != null) {
				rs.close();
			}
			connMgr.freeConnection(connectionPool, con);
		}
		
		return bResult;
	}
	
	public static boolean userExists(String userId) throws SQLException {
		boolean bResult = false;
		
		SystemLogManager sysLogMgr = SystemLogManager.getInstance();
		DBConnectionManager connMgr = DBConnectionManager.getInstance();
		String connectionPool = ConfigManager.getInstance().getConnectionPool();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		
		Connection con = connMgr.getConnection(connectionPool, TIMEOUT);
		PreparedStatement ps = null;
		ResultSet rs= null;
		
		try {
			if (con == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation("UserManager.userExists [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			}else{
				String strSQL="";
				strSQL="SELECT COUNT(*) FROM tbluser WHERE login_id=?";
				ps = con.prepareStatement(strSQL);
				ps.setString(1, userId);
				rs = ps.executeQuery();

				if (rs.next()){
					bResult = rs.getInt(1) > 0;
				}
			
			} 

		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("UserManager.userExists [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("UserManager.userExists [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (ps != null) {
					ps.close();
			}
			if (rs != null) {
				rs.close();
			}
			connMgr.freeConnection(connectionPool, con);
		}
		return bResult;
	}

	public static int resetPassword(String userId, String pass1) throws SQLException {
		int iCount = 0;
		String sMethod = "UserManager.resetPassword ";
		SystemLogManager sysLogMgr = SystemLogManager.getInstance();
		DBConnectionManager connMgr = DBConnectionManager.getInstance();
		String connectionPool = ConfigManager.getInstance().getConnectionPool();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		
		Connection con = connMgr.getConnection(connectionPool, TIMEOUT);
		PreparedStatement ps = null;
		
		try {
			if (con == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation(sMethod + " [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			}else{
				String strSQL="";
				strSQL="UPDATE tbluser " +
						" SET password=?,enabled='Y',locked='N',last_pwd_changed=sysdate,bad_pwd_no_attempts=0,reset_pwd_flag=0,last_login_timestamp='' " +
						" WHERE login_id=?";
				ps = con.prepareStatement(strSQL);
				ps.setString(1, MD5Helper.hash(pass1));
				ps.setString(2, userId);
				
				iCount = ps.executeUpdate();
				
			} 

		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation(sMethod + " [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation(sMethod + " [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (ps != null) {
					ps.close();
			}
			connMgr.freeConnection(connectionPool, con);
		}
		return iCount;
	}
	public static boolean isValidPassword(String userId,String password) throws SQLException {
		boolean bResult = false;
		
		SystemLogManager sysLogMgr = SystemLogManager.getInstance();
		DBConnectionManager connMgr = DBConnectionManager.getInstance();
		String connectionPool = ConfigManager.getInstance().getConnectionPool();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		
		Connection con = connMgr.getConnection(connectionPool, TIMEOUT);
		PreparedStatement ps = null;
		ResultSet rs= null;
		
		try {
			if (con == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation("UserManager.isValidPassword [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			}else{
				String strSQL="";
				strSQL="SELECT COUNT(*) FROM tbluser WHERE login_id=? and password=? ";
				ps = con.prepareStatement(strSQL);
				ps.setString(1, userId);
				ps.setString(2, MD5Helper.hash(password));
				rs = ps.executeQuery();

				if (rs.next()){
					bResult = rs.getInt(1) > 0;
				}
			
			} 

		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("UserManager.isValidPassword [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("UserManager.isValidPassword [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (ps != null) {
					ps.close();
			}
			if (rs != null) {
				rs.close();
			}
			connMgr.freeConnection(connectionPool, con);
		}
		return bResult;
	}
	public static int changePassword(String userId, String pass1) throws SQLException {
		int iCount = 0;
		String sMethod = "UserManager.changePassword ";
		SystemLogManager sysLogMgr = SystemLogManager.getInstance();
		DBConnectionManager connMgr = DBConnectionManager.getInstance();
		String connectionPool = ConfigManager.getInstance().getConnectionPool();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		
		Connection con = connMgr.getConnection(connectionPool, TIMEOUT);
		PreparedStatement ps = null;
		
		try {
			if (con == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation(sMethod + " [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			}else{
				String strSQL="";
				strSQL="UPDATE tbluser " +
						" SET password=?,enabled='Y',locked='N',last_pwd_changed=sysdate " +
						" WHERE login_id=?";
				ps = con.prepareStatement(strSQL);
				ps.setString(1, MD5Helper.hash(pass1));
				ps.setString(2, userId);
				
				iCount = ps.executeUpdate();
				
			} 

		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation(sMethod + " [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation(sMethod + " [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (ps != null) {
					ps.close();
			}
			connMgr.freeConnection(connectionPool, con);
		}
		return iCount;
	}

	public static String getCountryCode(String loginID) throws SQLException{
		
		String bResult = "";
		
		SystemLogManager sysLogMgr = SystemLogManager.getInstance();
		DBConnectionManager connMgr = DBConnectionManager.getInstance();
		String connectionPool = ConfigManager.getInstance().getConnectionPool();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		
		Connection con = connMgr.getConnection(connectionPool, TIMEOUT);
		PreparedStatement ps = null;
		ResultSet rs= null;

		try {
			if (con == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation("UserManager.getCountryCode [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			}else{
				String strSQL="";
				strSQL="SELECT country_code FROM tbluser WHERE login_id=? ";
				ps = con.prepareStatement(strSQL);
				ps.setString(1, loginID);
				rs = ps.executeQuery();

				if (rs.next()){
					bResult = rs.getString(1);
				}
			
			} 

		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("UserManager.userExists [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("UserManager.userExists [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (ps != null) {
					ps.close();
			}
			if (rs != null) {
				rs.close();
			}
			connMgr.freeConnection(connectionPool, con);
		}
		
		return bResult;
	}
}
